package cn.edu.sdjzu.xg.kcsj.security;

import cn.edu.sdjzu.xg.kcsj.util.*;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.TreeSet;

public class ResourceDao {
  private static ResourceDao resourceDao = new ResourceDao();

  private ResourceDao() {
  }

  public static ResourceDao getInstance() {
    return resourceDao;
  }

  private static final Logger log = LogManager.getLogger(ResourceDao.class);

  // 分页+条件
  public Collection<Resource> findAll(List conditionList, Pagination pagination, Connection connection) throws SQLException {
    //创建集合类对象，用来保存所有记录代表的resource对象
    Collection<Resource> resources = new TreeSet<Resource>();
    //准备预编译的语句
    int totalNum = CommonDao.getInstance().count(connection, "resource");
    //创建查询的主语句
    StringBuilder sql_select = new StringBuilder("SELECT * FROM resource ");
    //如果有条件，则生成条件子句，并附加到主语句后面
    if (conditionList != null) {
      String whereClause = Condition.toWhereClause(conditionList);
      sql_select.append(whereClause);
    }
    if (pagination != null) {
      String LimitClause = pagination.toLimitClause(totalNum);
      sql_select.append(LimitClause);
    }
    //执行预编译语句，结果集放在resultSet中
    PreparedStatement pstmt = connection.prepareStatement(sql_select.toString());
    ResultSet resultSet = pstmt.executeQuery();
    //遍历resultSet
    while (resultSet.next()) {
      int id = resultSet.getInt("id");
      String description = resultSet.getString("description");
      String no = resultSet.getString("no");
      String router = resultSet.getString("router");
      int parent_id = resultSet.getInt("parent_id");
      Resource resource_parent = ResourceService.getInstance().find(parent_id);
      String parent_ids_string = resultSet.getString("parent_ids");
      // parent_ids类型转换
      List<Integer> parent_ids_int = ListHelper.stringToIntegerList(parent_ids_string);
      boolean haveChild = resultSet.getBoolean("haveChild");
      boolean available = resultSet.getBoolean("available");
      int type = resultSet.getInt("type");
      Resource resource = new Resource(id, description, no, router, resource_parent, parent_ids_int, haveChild, available, type);
      resources.add(resource);
      log.debug(resource.toString());
    }
    JdbcHelper.close(resultSet, pstmt);
    return resources;
  }

  public Collection<Resource> findAll() throws SQLException {
    Connection connection = JdbcHelper.getConn();
    connection.setAutoCommit(false);
    Collection<Resource> resources = null;
    try {
      resources = this.findAll(null, null, connection);
      connection.commit();
    } catch (SQLException e) {
      e.printStackTrace();
      connection.rollback();
    } finally {
      //释放连接
      connection.setAutoCommit(true);
      JdbcHelper.close(connection);
    }
    return resources;
  }

  // 按id查一条
  public Resource find(int id, Connection connection) throws SQLException {
    Resource resource = null;
    //创建sql语句
    String find_sql = "select * from resource where id = ?";
    //在该连接上创建预编译语句对象
    PreparedStatement pstmt = connection.prepareStatement(find_sql);
    //为预编译参数赋值
    pstmt.setInt(1, id);
    //根据id查找
    ResultSet resultSet = pstmt.executeQuery();
    while (resultSet.next()) {
      String description = resultSet.getString("description");
      String no = resultSet.getString("no");
      String router = resultSet.getString("router");
      int parent_id = resultSet.getInt("parent_id");
      Resource resource_parent = ResourceService.getInstance().find(parent_id);
      String parent_ids_string = resultSet.getString("parent_ids");
      // parent_ids类型转换
      List<Integer> parent_ids_int = ListHelper.stringToIntegerList(parent_ids_string);
      boolean haveChild = resultSet.getBoolean("haveChild");
      boolean available = resultSet.getBoolean("available");
      int type = resultSet.getInt("type");
      resource = new Resource(id, description, no, router, resource_parent, parent_ids_int, haveChild, available, type);

    }
    JdbcHelper.close(resultSet, pstmt);
    return resource;
  }

  // 更新
  public boolean update(Resource resource, Connection connection) throws SQLException {
    //创建sql语句
    String updateSchool_sql = "update resource set description = ?, no = ?, router = ?, parent_id = ?, parent_ids = ?, haveChild = ?, available = ?, type = ? where id = ?";
    //在该链接上创建预编译语句对象
    PreparedStatement pstmt = connection.prepareStatement(updateSchool_sql);
    // 重新查找形成其parent_ids
    int parent_id = resource.getParent().getId();
    List<Integer> parent_ids_int = this.searchAllParents(parent_id, connection);
    //为预编译参数赋值
    pstmt.setString(1, resource.getDescription());
    pstmt.setString(2, resource.getNo());
    pstmt.setString(3, resource.getRouter());
    pstmt.setInt(4, resource.getParent().getId());
    pstmt.setString(5, ListHelper.listToString(parent_ids_int));
    pstmt.setBoolean(6, resource.isHaveChild());
    pstmt.setBoolean(7, resource.isAvailable());
    pstmt.setInt(8, resource.getType());
    pstmt.setInt(9, resource.getId());
    //根据执行结果，具体判断所要返回的值
    if (pstmt.executeUpdate() == 0) {
      return false;
    } else {
      return true;
    }
  }

  // 添加
  public boolean add(Resource resource, Connection connection) throws SQLException {
    //创建sql语句
    String addSchool_sql =
            "insert into Resource (description, no, router, parent_id, parent_ids, haveChild, available, type) values " +
                    "(?, ?, ?, ?, ?, ?, ?, ?);";
    //在该链接上创建预编译语句对象
    PreparedStatement pstmt = connection.prepareStatement(addSchool_sql);
    // 查找形成parent_ids
    int parent_id = resource.getParent().getId();
    List<Integer> parent_ids_int = this.searchAllParents(parent_id, connection);
    // 送到前台的parent_ids需要String形式
    String parent_ids_string = ListHelper.listToString(parent_ids_int);
    //为预编译参数赋值
    pstmt.setString(1, resource.getDescription());
    pstmt.setString(2, resource.getNo());
    pstmt.setString(3, resource.getRouter());
    pstmt.setInt(4, resource.getParent().getId());
    pstmt.setString(5, parent_ids_string);
    pstmt.setBoolean(6, resource.isHaveChild());
    pstmt.setBoolean(7, resource.isAvailable());
    pstmt.setInt(8, resource.getType());
    //获取执行结果
    int affectedRowNum = pstmt.executeUpdate();

    //根据结果，具体判断所要返回的值
    if (affectedRowNum == 1) {
      return true;
    } else {
      return false;
    }
  }

  // 删除
  public boolean delete(int id, Connection connection) throws SQLException {
    //创建sql语句
    String deleteResource_sql = "DELETE FROM resource WHERE id=?";
    //在该连接上创建预编译语句对象
    PreparedStatement pstmt = connection.prepareStatement(deleteResource_sql);
    //为预编译参数赋值
    pstmt.setInt(1, id);
    if (pstmt.executeLargeUpdate() == 1) {
      return true;
    } else {
      return false;
    }
  }

  public int searchParent(int id, Connection connection) throws SQLException {
    int parent_id = 0;
    //创建sql语句
    String selectParentId_sql = "select parent_id from resource where id = ?";
    //在该连接上创建预编译语句对象
    PreparedStatement pstmt = connection.prepareStatement(selectParentId_sql);
    //为预编译参数赋值
    pstmt.setInt(1, id);
    //根据id查找
    ResultSet resultSet = pstmt.executeQuery();
    while (resultSet.next()) {
      parent_id = resultSet.getInt("parent_id");
    }
    return parent_id;
  }

  public List<Integer> searchAllParents(int id, Connection connection) throws SQLException {
    List<Integer> parent_ids = new ArrayList<Integer>();
    parent_ids.add(id);
    String s = ListHelper.listToString(parent_ids);
    log.debug(s);
    int parent_id = 0;
    do {
      parent_id = this.searchParent(id, connection);
      log.debug(parent_id);
      if (parent_id != 0) {
        parent_ids.add(parent_id);
        s = ListHelper.listToString(parent_ids);
        log.debug(s);
        id = parent_id;
      }
    } while (parent_id != 0);
    return parent_ids;
  }
}
